/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Filter;

import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author Gjun
 */
public class DAO {
    private int gginin;
    static{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
    
    Connection getConnection() throws Exception{
        //Connection con = DriverManager.getConnection(
        //        "jdbc:sqlserver://127.0.0.1:1433;databaseName=javadb", "javadb", "javadb");
        Context x =new InitialContext();
        Context y=(Context)x.lookup("java:/comp/env");
        DataSource z =(DataSource)y.lookup("jdbc/mssql");
        Connection con = z.getConnection();
        return con;
    }

    public int modify(String sql,String param[]) throws Exception{
        int x =0;
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                for (int i =0; i< param.length;i++)
                    ps.setString(i+1, param[i]);
                x = ps.executeUpdate();
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return x;
    }
    
    public String account(String user) throws Exception{
        String paswd="";
        String sql="select paswd from account where username=?";
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                ps.setString(1,user);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    while (rs.next()){
                        paswd=rs.getString(1);
                    }                                      
                }finally{
                    rs.close();
                }
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return paswd;
    }
    
    public ArrayList<HashMap> query(String sql,String param[]) throws Exception{
        ArrayList<HashMap> rsList = new ArrayList<HashMap>();
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                for (int i =0; i< param.length;i++)
                    ps.setString(i+1, param[i]);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    java.sql.ResultSetMetaData meta = rs.getMetaData();
                    int count = meta.getColumnCount();
                    while (rs.next()){
                        HashMap map =new HashMap();
                        for(int i=1;i<=count;i++){
                            String key =meta.getColumnName(i);
                            String value =rs.getString(i);
                            map.put(key, value);
                        }
                        rsList.add(map);
                    }                                        
                }finally{
                    rs.close();
                }
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return rsList;
    }
    
    public ArrayList<HashMap> query2(String sql,String param[],String x,String y) throws Exception{
        ArrayList<HashMap> rsList = new ArrayList<HashMap>();
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql+" order by product_id offset "+x+" rows fetch next "+y+" rows only");
                for (int i =0; i< param.length;i++)
                    ps.setString(i+1, param[i]);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    java.sql.ResultSetMetaData meta = rs.getMetaData();
                    int count = meta.getColumnCount();                    
                    while (rs.next()){
                        HashMap map =new HashMap();
                        for(int i=1;i<=count;i++){
                            String key =meta.getColumnName(i);
                            String value =rs.getString(i);
                            map.put(key, value);
                        }
                        rsList.add(map);
                    }                                        
                }finally{
                    rs.close();
                }
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return rsList;
    }  
    
    public int GGININ() throws Exception{
        String sql="select COUNT(product_id) from product";
        Connection con =null;
        try{
           con=getConnection(); 
           PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    while (rs.next()){
                    gginin=Integer.parseInt(rs.getString(1));                  
                    }
                }finally{
                    rs.close();
                }
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
    return gginin;
    }
    
    public void m1(){
        String data[]={"1","2","3","4","5","6","7","8","9","10"};
        Connection con =null;
        try{
           con=getConnection(); 
           con.setAutoCommit(false);
           PreparedStatement ps = con.prepareStatement("insert into numtable(num) values(?)");
            try{
                for (String s:data){
                    ps.setString(1, s);
                    ps.executeUpdate();                
                }
                con.commit();
            }catch(Exception e){
                con.rollback();
            System.out.println(e);
            }
        }catch(Exception e){
            System.out.println(e);
        }               
    }
    public void m2(){
        String data[]={"1","2","3","4","5","6","7","8","9","A"};
        Connection con =null;
        try{
           con=getConnection(); 
           con.setAutoCommit(false);
           PreparedStatement ps = con.prepareStatement("insert into numtable(num) values(?)");
           Savepoint sav1=null;
            try{                
                    ps.setString(1, data[0]);
                    ps.executeUpdate();  
                    ps.setString(1, data[1]);
                    ps.executeUpdate();  
                    sav1=con.setSavepoint("");
                    ps.setString(1, data[2]);
                    ps.executeUpdate(); 
                    ps.setString(1, data[9]);
                    ps.executeUpdate();  
            }catch(Exception e){   
                con.rollback(sav1);
                System.out.println(e);
            }finally{
                con.commit();
            }
        }catch(Exception e){
            System.out.println(e);
        }               
    }
    
    
}
